package cn.edu.sdjzu.xg2106.dao;

import cn.edu.sdjzu.xg2106.domain.Department;
import cn.edu.sdjzu.xg2106.domain.School;
import cn.edu.sdjzu.xg2106.domain.University;
import cn.edu.sdjzu.xg2106.service.SchoolService;
import cn.edu.sdjzu.xg2106.service.UniversityService;
import cn.edu.sdjzu.xg2106.util.Condition;
import cn.edu.sdjzu.xg2106.util.JdbcHelper;
import cn.edu.sdjzu.xg2106.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class DepartmentDao {
    private static DepartmentDao departmentDao=null;
    //将唯一构造器设为私有，阻止在本类外生成本类的对象
    private DepartmentDao(){}
    public static DepartmentDao getInstance(){
        if (departmentDao == null){
            departmentDao=new DepartmentDao();
        }
        return departmentDao;
    }
    public boolean add(Department department)throws SQLException {
        Connection connection= JdbcHelper.getConn();
        PreparedStatement preparedStatement=connection.prepareStatement(
                "insert into department"+
                        "(id,description,no,remarks,school_id)"+
                        "values(?,?,?,?,?)");
        preparedStatement.setLong(1,department.getId());
        preparedStatement.setString(2,department.getDescription());
        preparedStatement.setString(3,department.getNo());
        preparedStatement.setString(4,department.getRemarks());
        preparedStatement.setLong(5,department.getSchool().getId());
        int rowAffected = preparedStatement.executeUpdate();
        return rowAffected>0;
    }
    public boolean update(Department department)throws SQLException {
        Connection connection= JdbcHelper.getConn();
        String sql_update=
                "update department set description=?,no=?,remarks=?,school_id=? where id=?";
        PreparedStatement preparedStatement_update = connection.prepareStatement(sql_update);
        preparedStatement_update.setString(1,department.getDescription());
        preparedStatement_update.setString(2,department.getNo());
        preparedStatement_update.setString(3,department.getRemarks());
        preparedStatement_update.setLong(4,department.getSchool().getId());
        preparedStatement_update.setLong(5,department.getId());
        int rowAffected = preparedStatement_update.executeUpdate();
        return rowAffected>0;
    }
    public Collection<Department> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException, SQLException {
        Collection<Department>desiredDeps = new HashSet<Department>();
        StringBuilder sql_select = new StringBuilder("select * from department");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"department");
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();

        while(resultSet_select.next()){
            int id = resultSet_select.getInt("id");
            String name = resultSet_select.getString("no");
            String description = resultSet_select.getString("description");
            String remarks = resultSet_select.getString("remarks");
            int schId = resultSet_select.getInt("uni_id");
            School school = SchoolService.getInstance().find(schId);
            Department desiredDep = new Department(id,description,name,remarks,school);
            desiredDeps.add(desiredDep);
        }
        JdbcHelper.close(resultSet_select,pstmt_select);
        return desiredDeps;
    }
    public Department find(Connection connection,int id) throws SQLException {
        Department desiredDep = null;
        String sql_select_id = "select * from department where id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if (resultSet_select_id.next()) {
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            int schId = resultSet_select_id.getInt("school_id");
            School school = SchoolService.getInstance().find(schId);
            desiredDep = new Department(id, description, no, remarks,school);
        }
        JdbcHelper.close(resultSet_select_id, pstmt_select_id);
        return desiredDep;
    }

    public int countAll(School school) throws SQLException{
        Connection connection = JdbcHelper.getConn();
        String sql_count_by_school_id = "SELECT COUNT(id) as  cnt_by_school_id FROM department WHERE school_id = ?";

        PreparedStatement pstmt_count_by_school_id =
                connection.prepareStatement(sql_count_by_school_id);

        pstmt_count_by_school_id.setInt(1, school.getId());

        int count_by_school_id = 0;
        ResultSet resultSet_count_by_school_id = pstmt_count_by_school_id.executeQuery();
        if (resultSet_count_by_school_id.next()){
            count_by_school_id = resultSet_count_by_school_id.getInt("cnt_by_school_id");
        }
        JdbcHelper.close(resultSet_count_by_school_id, pstmt_count_by_school_id, connection);
        return count_by_school_id;

    }
    public void delete(int id) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        PreparedStatement preparedStatement = connection.prepareStatement("delete from department " +
                " where id=?");
        preparedStatement.setInt(1, id);
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement, connection);
    }

}
